<?php


namespace commands;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class FlightFee extends BaseCommand
{
    protected static $defaultName = 'hkyf';
    const BCJG = '国际业务分公司';

    public function configure()
    {
        $this->setDescription('EMS航空运费，压缩包文件：*.SDYF.zip, *.SDNO.zip,*SDYS.zip');
        parent::configure();
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        parent::execute($input, $output);
        $dir = $this->getDataFilePath($input->getArgument('dir'));
        //解压文件
        $this->unzip(['*SDYF.zip', '*SDNO.zip', '*SDYS.zip'], $dir, $output);

        //合并文件
        $comboFile = $this->getDataFilePath($input->getArgument('dir') . '/temp/+.csv');
        $sdyf = $this->findFile($dir . '/temp/', 'SDYF_prov_51.txt', '>2');
        $sdno = $this->findFile($dir . '/temp/', 'SDNO_prov_51.txt', '>2');
        comboCsv($sdyf->getPathname(), $sdno->getPathname(), $comboFile);
        $this->doCsv($comboFile, $output, function ($row, $n) {
            if ($n == 0) return;
            $code = $this->getCellFromRow($row, 'E');
            $this->processField('emsZbqf', $code, $row, $n);
            $this->processField('emsTsdd', $code, $row, $n);
            $this->processField('emsTszf', $code, $row, $n);
            $this->processField('bgZbqf', $code, $row, $n);
            $this->processField('bgTsdd', $code, $row, $n);
            $this->processField('bgBszf', $code, $row, $n);
        }, 0);
        $dest = $dir . '/hkyf_qfb.xlsx';

        $subtotal = [];
        $this->export($dest, './tpl/emshkyf_qfb.xlsx', function (Spreadsheet $spreadsheet) use (&$subtotal) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $this->processExport($this->data, $first_row, $sheet, $subtotal);

        });
        $this->export($dir . '/hkyf_qfb2.xlsx', './tpl/emshkyf_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $this->processExport($this->data2, $first_row, $sheet, $subtotal);
        });
        $sdys = $this->findFile($dir . '/temp/', 'SDYS_prov_*.txt', '>=2', 'SDYS');
        $this->doCsv($sdys->getPathname(), $output, function ($row, $n) {
            if (isset($this->data['Q'])) {

            } else {
                $this->data['Q'] = 0;
            }
            $this->data['Q'] += floatval($this->getCellFromRow($row, 'Q'));
        });
        $dest = $dir . '/hkyf_sfb.xlsx';
        $this->export($dest, './tpl/emshkyf_sfb.xlsx', function (Spreadsheet $spreadsheet) use ($subtotal) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $row = $first_row;
            foreach ($this->data as $city => $data) {
                if (!is_array($data)) continue;
                $sheet->setCellValue('A' . $row, $city);
                if ($city == self::BCJG) {
                    $sheet->setCellValueExplicit('B' . $row, round($this->data['Q'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('C' . $row, round($subtotal['D'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('D' . $row, round($subtotal['H'], 2), DataType::TYPE_NUMERIC);
                }
                $sheet->setCellValue('E' . $row, sprintf('=SUM(B%d:D%d)', $row, $row));
                $row++;
            }
            $this->setSheetSubtotal($sheet, $first_row, $row, 'E');
        }
        );
        $this->rmTemp($dir, $output);
    }

    public function emsZbqf($row, $n)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            if (iconvStr($row[getColIndex('R')]) == '非本省组开') {
                return $row[getColIndex('Q')];
            } else {
                return 0;
            }
        }
        return 0;
    }

    public function emsTsdd($row)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            if (iconvStr($row[getColIndex('R')]) == '本省组开') {
                return $row[getColIndex('Q')];
            } else {
                return 0;
            }
        }
        return 0;
    }

    public function emsTszf($row)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            if (iconvStr($row[getColIndex('R')]) == '本省速递收寄本省速递承运') {
                return $row[getColIndex('Q')];
            } else {
                return 0;
            }
        }
    }

    public function bgZbqf($row)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            return 0;
        } else {
            if (iconvStr($row[getColIndex('R')]) == '非本省组开') {
                return $row[getColIndex('Q')];
            }
            return 0;
        }
    }

    public function bgTsdd($row)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            return 0;
        } else {
            if (iconvStr($row[getColIndex('R')]) == '本省组开') {
                return $row[getColIndex('Q')];
            }
            return 0;
        }
    }

    public function bgBszf($row)
    {
        if ($row[getColIndex('H')] == 'EEMF') {
            return 0;
        } else {
            if (iconvStr($row[getColIndex('R')]) == '本省速递收寄本省速递承运') {
                return $row[getColIndex('Q')];
            }
            return 0;
        }

    }

    public function processExport($data, $first_row, Worksheet $sheet, &$subtotal)
    {
        $row = $first_row;
        foreach ($data as $city => $line) {
            $sheet->setCellValue('A' . $row, $city);
            $sheet->setCellValueExplicit('B' . $row, round($line['emsZbqf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('C' . $row, round($line['emsTsdd'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('D' . $row, round($line['emsTszf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('E' . $row, sprintf('=SUM(B%d:D%d)', $row, $row));
            $sheet->setCellValueExplicit('F' . $row, round($line['bgZbqf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('G' . $row, round($line['bgTsdd'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('H' . $row, round($line['bgBszf'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('I' . $row, sprintf('=SUM(F%d:H%d)', $row, $row));
            $sheet->setCellValue('J' . $row, sprintf('=E%d+I%d', $row, $row));
            $row++;
        }
        $this->setSheetSubtotal($sheet, $first_row, $row, 'J', $subtotal);
    }
}
